#include "MysqlMyHandler.h"

MysqlMyHandler::MysqlMyHandler()
{
    /*
    if(!openState)
    {
        qDebug()<<"******mysql database connect failed!!!******";
        qDebug()<<"不能连接"<<"connect to mysql error"<<m_db.lastError().text();
        return;
    }
    else                 //open database success
    {
        qDebug()<<"******mysql database connect successed. ******";
    }
    */

    //从连接名字数组中取一个获取一个可以使用设置的连接名字
    if(g_v_conn_name_stat.size()==10)
    {
        qDebug() <<"the connetion name pool is full,connction name number is 10 ";
    }
}

MysqlMyHandler::~MysqlMyHandler()
{
    //在这里析构所有的操作对象

}

//用户可以修改连接名和其它 mysql服务器连接参数
void MysqlMyHandler::initConn(QString& hostname,QString& username,QString& password,
            QString& dbname,int& port,QString& socket,unsigned long& clientflag,QString& con_name)
{
    this->m_hostName = hostname;
    this->m_userName = username;
    this->m_password = password;
    this->m_dbname = dbname;
    this->m_port = port;
    this->m_socket= socket;
    this->m_clientflag = clientflag;
    //this->m_conn_name_stat[0].con_name = con_name;  //数组元素的添加;

    //设置连接池name 和对应连接状态的设置
    struct Conn_name_stat conn_name_stat;
    conn_name_stat.con_name=con_name;

    // 启动数据库引擎
    this->m_db = QSqlDatabase::addDatabase("QMYSQL",  conn_name_stat.con_name);
    //数据库引擎load出错进行 retry
     if(false == m_db.isValid() )
     {
         try {
             int count= 0;
             do{
                  m_db = QSqlDatabase::addDatabase("QMYSQL",  conn_name_stat.con_name);
                 count++;
                 if(true==m_db.isOpen())
                 {
                     break;
                 }
             } while (count < 3);
             if(m_db.isValid()){
                 throw " already retry 3 times,cannnot setup QMYSQL ENGINE";
             }
         } catch (const char* msg) {
                qDebug() << msg << endl;
         }
     }
     //将连接放进连接池
     conn_name_stat.is_available=false;
     g_v_conn_name_stat.push_back(conn_name_stat);
     //设置连接
     setDBConn();
    // m_myquery.exec("select * from saletable;");
    //qDebug()<< m_myquery.isActive() << m_myquery.isValid();
}

void MysqlMyHandler::setDBConn()
{
    m_db.setHostName(m_hostName);
    m_db.setDatabaseName(m_dbname);
    m_db.setPort(m_port);
    m_db.setUserName(m_userName);
    m_db.setPassword(m_password);
    openState = m_db.open();
    if(openState==true)
    {
        qDebug()<<"database open successed.";
    }
    else
    {
        //使用当前的参数设置再次尝试连接三次
        int count=0;
        do
        {
            openState = m_db.open();
            count++;
        }while (count<3);
        if(openState==false)
        {
           qDebug()<<m_db.lastError();
        }
    }
}

const QSqlResult*  MysqlMyHandler::selectData(const QString& sqlStatement)
{

    QSqlQuery m_myquery(m_db);
    m_myquery.exec(sqlStatement);   //直接使用传入的sql
    //qDebug()<< m_myquery.isActive() << m_myquery.isValid();
    while(m_myquery.next())
    {
        QSqlRecord rec = m_myquery.record();   //获取表的数据记录
        int col_num=rec.count();
        //输出记录的值，res的结果集中有几行字段就输出几行值
        for (int i=0; i<col_num ; i++)
        {
            qDebug()<< rec.value(i);
        }
        qDebug()<<"******************************";
    }
    int row_num = m_myquery.size();
    qDebug() << "********数据查询结束*************\n********结果的行数：" << row_num ;
    const QSqlResult*  res = m_myquery.result();
    if(res==nullptr)
    {
        qDebug()<<m_db.lastError();
    }
    return  res;
}

//使用Order by 查询  select book_price from saletable order by
const QSqlResult* MysqlMyHandler::selectDataOrder(const QString& table_name, const QString &col_name)
{
    const QSqlResult*  res = m_myquery.result();
    QString sql1 = "SELECT" + table_name;
    QString sql2 = "";





    while(m_myquery.next())
    {
        QSqlRecord rec = m_myquery.record();
    }


    return  res;
}

void MysqlMyHandler::closeConn()
{
    if(m_db.isOpen()==true)
    {
          m_db.close();
    }
    return;
}

const QSqlResult*  MysqlMyHandler::multilSqlStatemtSelect(const QString& sqlstatement)
{
    const QSqlResult* res;
    QString sqlstr_cp = sqlstatement;
    //“;” 分号检查;
    if (sqlstr_cp.endsWith(";")) {
        qDebug() <<"check the '; OK. ";
    }
    else {
        //添加";"结尾;
        sqlstr_cp += ";";
        qDebug() << sqlstr_cp;
    }
    QStringList strlist = sqlstr_cp.split(";");
    QList<QString>::iterator it = strlist.begin(),itend=strlist.end();
    int i=0;
    qDebug() << "同时输入的多条SQ语句已经拆分的单条数目："  << strlist.size()-1 ;
    for (i=0; i<strlist.size()-1;i++)
    {
        qDebug() << "正在执行的数据查询动作:" << strlist[i] +";" << "第" << i+1 << "次";
        res = selectData(strlist[i] + ";");         //循环调用查询基础接口
    }

    return res;
}

bool MysqlMyHandler::checkDBStatus(QString conname)
{
    for(int i=0;i<10;i++)
    {
        if(g_v_conn_name_stat[i].con_name==conname)
        {
            return g_v_conn_name_stat.at(i).is_available;
        }
    }
    return 0;
}

QMap<QString, QString> MysqlMyHandler::getTableInfo(QString tablename)
{
    QMap<QString ,QString> tableMap;
    QSqlQuery m_myquery(m_db);  //将QSqlQuery对象与数据库连接对象绑定
    //简单的字符串拼接
    QString str = "DESCRIBE "  + tablename + ";";
    //获取表的字段名
    QString table = tablename;
    qDebug() <<str;
    m_myquery.prepare(str);
    if(m_myquery.exec())
    {
        while (m_myquery.next())
        {
            tableMap[m_myquery.value(0).toString()] = m_myquery.value(1).toString();
        }
        return tableMap;
    }
    //输出SQL错误
    else {
        qDebug()<<m_myquery.lastError();
        return  tableMap;
    }
}

void MysqlMyHandler::printTableStruct(QString tablename)
{
    QMap<QString ,QString> tableMap;
    QSqlQuery m_myquery(m_db);  //将QSqlQuery对象与数据库连接对象绑定
    //简单的字符串拼接
    QString str = "DESCRIBE "  + tablename + ";";
    //获取表的字段名
    QString table = tablename;
    qDebug() << str;
    m_myquery.prepare(str);
    if(m_myquery.exec())
    {
        int index= 0;
        while (m_myquery.next())
        {
            qDebug()<< "字段:" << index << m_myquery.value(0).toString() << "字段类型:" << m_myquery.value(1).toString();
            tableMap[m_myquery.value(0).toString()] = m_myquery.value(1).toString();
            index++;
        }
        return ;
    }
    //输出SQL错误
    else {
        qDebug()<<m_myquery.lastError();
        return;
    }
}

bool MysqlMyHandler::hasFeaturesofTransaction()
{
     return m_db.driver()->hasFeature(QSqlDriver::Transactions);
}

bool MysqlMyHandler::startTransaction()
{
    return m_db.transaction();

}

bool MysqlMyHandler::commitTransaction()
{
    return m_db.commit();
}

//简化用户输入
// 参数用户输入 1.table 名字，2. key-value的形式的数据
bool MysqlMyHandler::insertData(const QString tablename,QVariantMap map)
{
    qDebug() <<"开始执行插入数据操作。";
    QSqlQuery m_myquery(m_db);
    qDebug() <<"数据库是否打开：" <<m_db.isOpen();
    QMap<QString , QString> tableContentMap; //表的信息
    bool re =  m_db.tables().contains(tablename);
    qDebug() <<"是否包含" << tablename + ":" << re;
    if(false == re)
    {
        qDebug() <<tablename << "  not exit ";
        return false;
    }
    else {
        qDebug() <<"开始执行插入数据操作的表字段获取。";
        tableContentMap =  getTableInfo(tablename);
        //表没有字段
        if(tableContentMap.isEmpty())
        {
            return false;
        }
    }
    //insert into testtable values (),(),()
    QString sqlPart1 = QString("INSERT INTO %1 (").arg(tablename);
    QString sqlPart2 = QString(" VALUES (");
    //要保证传进来的字段是正确的
    QMapIterator<QString,QString> it(tableContentMap);
    while(it.hasNext())
    {
        it.next();
        sqlPart1.append(QString("%1").arg(it.key()));
        sqlPart2.append("?");
        if(it.hasNext())
        {
            sqlPart1.append(", ");
            sqlPart2.append(", ");
        }
    }
    sqlPart1.append(") ");
    sqlPart2.append(");");
    sqlPart1.append(sqlPart2);
    qDebug() <<" 将要执行的数据插入 SQL 语句是:" << sqlPart1;
    if(!m_myquery.prepare(sqlPart1))
    {
        return  false;
    }
    QMapIterator<QString,QString> it2(tableContentMap);
    while(it2.hasNext())
    {
        it2.next();
        m_myquery.addBindValue(map[it2.key()]);
    }
    return m_myquery.exec();
}

//删除某一指定行 确定的表达式
//限制条件为 where + 表达式
//like + 通配符 可看删除多行
bool MysqlMyHandler::deleteData(const QString sqlStatement)
{
    qDebug() <<"执行删除动作..." << sqlStatement;
    QSqlQuery m_myquery(m_db);
    QString sqldelete= sqlStatement;
    bool flag = m_myquery.exec(sqldelete);
    if(true==flag)
    {
        qDebug() <<"执行删除动作完成。";
        return flag;
    }
    else {
          qDebug() <<"执行删除动作出错" << m_myquery.lastError();
          return false;
    }
}

bool MysqlMyHandler::updateData(const QString sqlStatement)
{
    qDebug() << "******开始执行 update 操作*****" << sqlStatement;
    QSqlQuery m_myquery(m_db);
    QString sqlupdate = sqlStatement;
    bool  flag = m_myquery.exec(sqlStatement);
    if(true==flag)
    {
        qDebug() <<"执行更新动作完成。";
        return  flag;
    }
    else {
          qDebug() <<"执行更新动作出错" << m_myquery.lastError();
          return false;
    }
}

// delete from table where [coloum ..= ..]
bool MysqlMyHandler::SqlStatementDelete(const QString tablename, const QString exp)
{
    bool re;
    //封装后完整的 SQL 语句头
    QString deletesql = "DELETE FROM ";
    QString sql1 = tablename + " ";
    QString sql2 = "where " + exp+";";
    //给 SQL 语句头添加后续指定的表明和定位表达式
    deletesql.append(sql1);
    deletesql.append(sql2);
    qDebug() << "将要执行的删除操作：" << deletesql;
    re = deleteData(deletesql);
    if(false == re)
    {
        qDebug() << "delete data in  " << tablename + " failed!" ;
    }
    return  re;
}

//update更新指定表的某个数据
bool MysqlMyHandler::SqlStatementUpdate(const QString tablename,const QString exp)
{
    bool re;
    QString updatesql = "UPDATE FROM ";
    QString sql1 = tablename;
    QString sql2 = exp+";";
    updatesql.append(sql1);
    updatesql.append(sql2);
    updateData(updatesql);
    re = m_myquery.exec(updatesql);
    if(false == re)
    {
        qDebug() <<"update" + tablename << "failed!";
        qDebug() << m_myquery.lastError();
    }
    return re;
}

//删除指定表的所有行的数据，使用SQL TRUNCATE不支持回滚的
bool MysqlMyHandler::deleteAllDataByTruncate(const QString tablename)
{
   QString sqlDeleteAllData = "TRUNCATE " + tablename + ";";
   bool re=m_myquery.exec(sqlDeleteAllData);
   if(false == re)
   {
       qDebug() << m_myquery.lastError();
   }
   else {
       qDebug() <<"delete all data  of" << tablename <<" successed.";
   }
   return re;
}

//删除所有的数据
bool MysqlMyHandler::deleteAllDataByDelete(const QString tablename)
{
    QString sqlDeleteAllData = "delete from " + tablename + ";";
    bool re=m_myquery.exec(sqlDeleteAllData);
    if(false == re)
    {
        qDebug() << m_myquery.lastError();
    }
    else {
        qDebug() <<"delete all data  of" << tablename <<" successed.";
    }
    return re;
}

